C:/Projekte/RAIS/FillCustomQueryTableCL/QueryTable.cs

Go to the documentation of this file.

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using RAIS.DataAccessLayer;

namespace FillCustomQueryTableCL
{
    public static class QueryTable
    {
        #region Fields
        
        private static Dictionary<string, Query> queries = new Dictionary<string, Query>();



        #endregion
                public static void FillQueryTable(string connectionString, string queriesFileName)
        {
                        Query.InitQuery(connectionString);
                        Parameter.InitParameter(connectionString);
                        queries = Query.Create(connectionString, queriesFileName);

            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();

                StringBuilder sbParams = new StringBuilder();
                StringBuilder sbQueries = new StringBuilder();

                XmlWriter xwParams = XmlWriter.Create(sbParams);
                XmlWriter xwQueries = XmlWriter.Create(sbQueries);

                xwParams.WriteStartElement("Parameters");
                xwQueries.WriteStartElement("Queries");

                foreach (Query query in queries.Values)
                    query.WriteToXML(xwQueries, xwParams);

                xwParams.WriteEndElement();
                xwQueries.WriteEndElement();

                xwParams.Close();
                xwQueries.Close();


                WriteData(sbQueries.ToString(), sbParams.ToString(), sqlConnection);
            }
        }

        #region Private methods

        private static void WriteData(String queries, String parrameters, SqlConnection sqlConnection)
        {
            using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
            {
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "usp_FillQueryTable";
                DataAccessUtilities.AddParameter(sqlCommand, "@Queries", queries);
                DataAccessUtilities.AddParameter(sqlCommand, "@Parameters", parrameters);
                sqlCommand.Parameters[0].SqlDbType = SqlDbType.Xml;
                sqlCommand.Parameters[1].SqlDbType = SqlDbType.Xml;
                sqlCommand.ExecuteNonQuery();
            }
        }

        #endregion
    }
}